package com.blb.dao.impl;


import com.blb.dao.TopicDao;
import com.blb.pojo.Topic;
import com.blb.utils.DBUtils;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

@Repository
public class TopicDaoImpl implements TopicDao {
    @Override
    public void add(Topic topic) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into s_topic(id,message,image,time,user_id) values(?,?,?,?,?)";
        DBUtils.executeUpdate(conn,sql,topic.getId(),topic.getMessage(),topic.getImage(),topic.getTime(),topic.getUserId());
        DBUtils.evict(conn);
    }

    /**
     * 删除
     * @param id
     * @throws SQLException
     */
    @Override
    public void delete(String id) throws SQLException {
        Connection conn = DBUtils.getConn();
        DBUtils.executeUpdate(conn,"delete from s_topic where id = ?", id);
        DBUtils.evict(conn);
    }

    /**
     * 朋友圈信息
     *
     * @return
     */
    @Override
    public List<Topic> Topiclist() throws Exception {
        Connection conn = DBUtils.getConn();
//        String sql = "select * from s_topic order by time desc";
        String sql = "select sto.*,IFNULL(num1,0) num from s_topic sto\n" +
                "LEFT JOIN (select topic_id,count(topic_id) num1 from s_comment sc GROUP BY sc.topic_id) a on  sto.id=a.topic_id ORDER BY time desc";
        List<Topic> list = DBUtils.getList(Topic.class, conn, sql);
        DBUtils.evict(conn);
        return list;
    }
}
